﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class UrlDao
    {
        public bool Delete(SqlConnection conn, int urlId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF @UserId \tIS NULL ");
            builder.Append("\tDELETE FROM [dbo].[Url] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[UrlId] = @UrlId ");
            builder.Append("\t\tAND [UserId]  IS NULL ");
            builder.Append("\t\t ");
            builder.Append("ELSE ");
            builder.Append("\tDELETE FROM [dbo].[Url] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[UrlId] = @UrlId ");
            builder.Append("\t\tAND [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@UrlId", SqlDbType.Int);
            commandParameters[0].Value = urlId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            if (userId == string.Empty)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = userId;
            }
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, UrlData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Url] ");
            builder.Append("( ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[UrlNo], ");
            builder.Append("\t[UrlDesc], ");
            builder.Append("\t[Url] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@UserId, ");
            builder.Append("\t@UrlNo, ");
            builder.Append("\t@UrlDesc, ");
            builder.Append("\t@Url ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[4];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            if (data.UserIdIsNull)
            {
                commandParameters[0].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0].Value = data.UserId;
            }
            commandParameters[1] = new SqlParameter("@UrlNo", SqlDbType.Int);
            if (data.UrlNoIsNull)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = data.UrlNo;
            }
            commandParameters[2] = new SqlParameter("@UrlDesc", SqlDbType.NText);
            if (data.UrlDescIsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.UrlDesc;
            }
            commandParameters[3] = new SqlParameter("@Url", SqlDbType.NVarChar, 200);
            if (data.UrlIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.Url;
            }
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectAll(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("IF @UserId \tIS NULL ");
            builder.Append("\tSELECT ");
            builder.Append("\t\t[UrlId], ");
            builder.Append("\t\t[UserId], ");
            builder.Append("\t\t[UrlNo], ");
            builder.Append("\t\t[UrlDesc], ");
            builder.Append("\t\t[Url] ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[Url] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[UserId] IS NULL ");
            builder.Append("\tORDER BY ");
            builder.Append("\t\t[UrlNo] ASC ");
            builder.Append("ELSE ");
            builder.Append("\tSELECT ");
            builder.Append("\t\t[UrlId], ");
            builder.Append("\t\t[UserId], ");
            builder.Append("\t\t[UrlNo], ");
            builder.Append("\t\t[UrlDesc], ");
            builder.Append("\t\t[Url] ");
            builder.Append("\tFROM ");
            builder.Append("\t  \t  [dbo].[Url] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[UserId] = @UserId ");
            builder.Append("\tORDER BY ");
            builder.Append("\t\t[UrlNo] ASC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            if (userId == string.Empty)
            {
                commandParameters[0].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0].Value = userId;
            }
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Url";
            return set.Tables[0];
        }

        public DataTable SelectByUrlId(SqlConnection conn, int urlId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("IF @UserId IS NULL ");
            builder.Append("\tSELECT ");
            builder.Append("\t\t[UrlId], ");
            builder.Append("\t\t[UserId], ");
            builder.Append("\t\t[UrlNo], ");
            builder.Append("\t\t[UrlDesc], ");
            builder.Append("\t\t[Url] ");
            builder.Append("\tFROM ");
            builder.Append("\t    [dbo].[Url] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[UrlId] = @UrlId ");
            builder.Append("\t\tAND [UserId] IS NULL ");
            builder.Append("ELSE ");
            builder.Append("\tSELECT ");
            builder.Append("\t\t[UrlId], ");
            builder.Append("\t\t[UserId], ");
            builder.Append("\t\t[UrlNo], ");
            builder.Append("\t\t[UrlDesc], ");
            builder.Append("\t\t[Url] ");
            builder.Append("\tFROM ");
            builder.Append("\t    [dbo].[Url] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[UrlId] = @UrlId ");
            builder.Append("\t\tAND [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@UrlId", SqlDbType.Int);
            commandParameters[0].Value = urlId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            if (userId == string.Empty)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = userId;
            }
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Url";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, UrlData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF @UserId IS NULL ");
            builder.Append("\tUPDATE [dbo].[Url] ");
            builder.Append("\tSET   ");
            builder.Append("\t    [UrlNo] = @UrlNo, ");
            builder.Append("\t    [UrlDesc] = @UrlDesc, ");
            builder.Append("\t    [Url] = @Url ");
            builder.Append("\tWHERE ");
            builder.Append("\t    [UrlId] = @UrlId ");
            builder.Append("\t    AND   [UserId] IS NULL ");
            builder.Append("ELSE ");
            builder.Append("\tUPDATE [dbo].[Url] ");
            builder.Append("\tSET   ");
            builder.Append("\t    [UrlNo] = @UrlNo, ");
            builder.Append("\t    [UrlDesc] = @UrlDesc, ");
            builder.Append("\t    [Url] = @Url ");
            builder.Append("\tWHERE ");
            builder.Append("\t    [UrlId] = @UrlId ");
            builder.Append("\t    AND   [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[5];
            commandParameters[0] = new SqlParameter("@UrlId", SqlDbType.Int);
            commandParameters[0].Value = data.UrlId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            if (data.UserIdIsNull)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = data.UserId;
            }
            commandParameters[2] = new SqlParameter("@UrlNo", SqlDbType.Int);
            if (data.UrlNoIsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.UrlNo;
            }
            commandParameters[3] = new SqlParameter("@UrlDesc", SqlDbType.NText);
            if (data.UrlDescIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.UrlDesc;
            }
            commandParameters[4] = new SqlParameter("@Url", SqlDbType.NVarChar, 200);
            if (data.UrlIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.Url;
            }
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

